package com.hnyyjsxy.dao.impl;

import com.hnyyjsxy.dao.TeacherDao;
import com.hnyyjsxy.domain.*;
import com.hnyyjsxy.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

/**
 * @date 2021/02/05
 */
public class TeacherDaoImpl implements TeacherDao {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    /**
     * 通过账号查询该老师的信息信息
     * @param account 账号
     * @return 老师信息
     */
    @Override
    public Teacher getTeacherByAccount(String account) {
        Teacher teacher = null;
        try{
            String sql = "SELECT * FROM `teacher` WHERE account=?";
            teacher = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Teacher>(Teacher.class),account);
        }catch(Exception e){
        }
        return teacher;
    }

    /**
     * 查询该床号是否已有人
     * @param building 楼栋号
     * @param room 房间号
     * @param bed 床号
     * @return 如果已经有人了 就返回true
     */
    @Override
    public boolean queryBedExist(String building, String room, String bed) {
        int count = -1;
        try{
            String sql = "SELECT count(*) FROM `student` WHERE building=? AND room=? AND bed=?";
            count = jdbcTemplate.queryForObject(sql,Integer.class,building,room,bed);
        }catch(Exception e){
        }
        return count!=0;
    }

    /**
     * 查询该楼栋下的所有调换宿舍的申请
     * @param building 楼栋号
     * @return 学生申请的集合
     */
    @Override
    public List<ApplyFor> queryStudentApply(String building) {
        List<ApplyFor> list = null;
        try{
            String sql = "SELECT * FROM `apply_for` WHERE old_building=? AND judge = 0";
            list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<ApplyFor>(ApplyFor.class), building);
        }catch (Exception e){
        }
        return list;
    }

    /**
     * 宿舍老师同意了学生调换宿舍的申请
     * @param sid
     * @param room
     * @param bed
     */
    @Override
    public void consentApply(String sid, String room, String bed) {
        //修改学生表中存储的房间号和床号
        String sql = "UPDATE student SET room=?,bed=? WHERE sid=?";
        jdbcTemplate.update(sql,room,bed,sid);
        //删除申请表中该学生的这条申请
        sql="DELETE FROM apply_for WHERE sid=?";
        jdbcTemplate.update(sql,sid);
    }

    /**
     * 宿舍老师拒绝了学生调换宿舍的申请
     * @param sid
     */
    @Override
    public void refusedApply(String sid) {
        //删除申请表中该学生的这条申请
        String sql="DELETE FROM apply_for WHERE sid=?";
        jdbcTemplate.update(sql,sid);
    }

    /**
     * 添加学生违纪情况
     * @param sid 学号
     * @param cause 违纪原因
     */
    @Override
    public void addStudentViolate(String sid, String cause) {
        String sql = "INSERT INTO violate(sid,cause,vdate) VALUE(?,?,date(now()))";
        jdbcTemplate.update(sql,sid,cause);
    }

    /**
     * 添加外访人员记录
     * @param stranger 外访人员信息
     */
    @Override
    public void addStrangerRecord(Stranger stranger) {
        String sql = "INSERT INTO stranger(id_number,sname,building,room,cause,sdate) VALUE(?,?,?,?,?,date(NOW()))";
        jdbcTemplate.update(sql,stranger.getId_number(),stranger.getSname(),stranger.getBuilding(),stranger.getRoom(),stranger.getCause());
    }

    /**
     * 外访人员查询，通过楼栋和姓名查询 查询共有多少条数据
     * @param building 楼栋号
     * @param sname 外访人员姓名
     * @return 外访人员条数
     */
    @Override
    public int queryStrangerCount(String building, String sname) {
        String sql = "SELECT count(*) FROM stranger where 1=1 ";
        StringBuilder sb = new StringBuilder(sql);
        List list = new ArrayList();
        //楼栋号
        if (building!=null && building.length()>0 && !"null".equals(building)){
            sb.append(" AND building=? ");
            list.add(building);
        }
        //姓名
        if (sname!=null && sname.length()>0 && !"null".equals(sname)){
            sb.append(" AND sname=? ");
            list.add(sname);
        }
        //将拼接好的StringBuilder转换为String
        sql = sb.toString();
        //返回查询到的个数
        return jdbcTemplate.queryForObject(sql,Integer.class,list.toArray());
    }

    /**
     * 外访人员查询，通过楼栋和姓名查询
     * @param building 楼栋号
     * @param sname 姓名
     * @param begin 开始位置
     * @param end 查询多少条
     * @return 外访人员信息 list集合
     */
    @Override
    public List<Stranger> queryStrangerRecord(String building, String sname, int begin, int end) {
        String sql = "SELECT * FROM stranger where 1=1 ";
        StringBuilder sb = new StringBuilder(sql);
        List list = new ArrayList();
        //楼栋号
        if (building!=null && building.length()>0 && !"null".equals(building)){
            sb.append(" AND building=? ");
            list.add(building);
        }
        //姓名
        if (sname!=null && sname.length()>0 && !"null".equals(sname)){
            sb.append(" AND sname=? ");
            list.add(sname);
        }
        sb.append(" LIMIT ?,?");
        list.add(begin);
        list.add(end);
        //将拼接好的StringBuilder转换为String
        sql = sb.toString();
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Stranger>(Stranger.class),list.toArray());
    }

    /**
     * 查询该卡号的是否存在
     * @param idCard
     * @return
     */
    @Override
    public DoorForbidding queryCard(String idCard) {
        DoorForbidding doorForbidding = null;
        try{
            String sql = "SELECT * FROM `door_forbidding` WHERE id_card=?";
            doorForbidding = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<DoorForbidding>(DoorForbidding.class),idCard);
        }catch(Exception e){
        }
        return doorForbidding;
    }

    /**
     * 通过学号删除学生信息
     * @param sid 学号
     */
    @Override
    public void delStudentBySid(String sid) {
        //删除账号表中学生数据
        String sql1 = "DELETE FROM account_sheet WHERE account=?";
        //学生表中
        String sql2 = "DELETE FROM student WHERE sid=?";
        //门禁表中
        String sql3 = "DELETE FROM door_forbidding WHERE sid=?";
        //违纪表中
        String sql4 = "DELETE FROM violate WHERE sid=?";
        //执行sql
        jdbcTemplate.update(sql1,sid);
        jdbcTemplate.update(sql2,sid);
        jdbcTemplate.update(sql3,sid);
        jdbcTemplate.update(sql4,sid);

    }

    /**
     * 通过班级查询学生信息
     * @param squad 班级
     * @param building 楼栋号
     * @return
     */
    @Override
    public List<Student> queryStudentBySquad(String squad,String building) {
        List<Student> list = null;
        try{
            String sql = "SELECT * FROM `student` WHERE squad=? AND building=?";
            list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Student>(Student.class),squad,building);
        }catch(Exception e){

        }
        return list;
    }
}
